package com.share.beta.web;

import com.alibaba.fastjson.JSONObject;
import com.share.common.common.Result;
import com.share.common.utils.DbUtil;
import com.share.common.utils.HttpUtil;
import io.swagger.annotations.Api;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

@Slf4j
@Api("db模块")
@RestController
public class DbController {
    @Autowired
    private DataSource dataSource;

    /* 配置restful风格访问@PathVariable注解必须写,不然获取不到值 */
    @RequestMapping(value = "/sql/{sql}", produces = "application/json; charset=utf-8")
    public Result remoteSql(@PathVariable String sql, HttpServletRequest request, HttpServletResponse response) throws Exception {
        log.info("参数的sql:" + sql);
        if (sql == null || "".equals(sql.trim())) {
            return Result.failure("参数错误");
        }

        Object result = executeSql(sql);
        return Result.success(result);
    }


    @RequestMapping("/remote/sql")
    public Result contextsql(@RequestBody String sql) throws Exception {
        log.info("参数的sql:" + sql);
        if (sql == null || "".equals(sql.trim())) {
            return Result.failure("参数错误");
        }

        List<Object> list = new LinkedList<Object>();
        String[] split = sql.split(";");
        for (String strsql : split) {
            try {
                Object result = executeSql(strsql);
                list.add(result);
            } catch (Exception e) {
                log.info("执行异常" + e);
                return Result.failure("执行异常" + e);
            }
        }
        return Result.success(list);
    }

    /* 直接在页面上测试远程接口 */
    @RequestMapping("/remote/api")
    public Result remoteApi(@RequestBody JSONObject data) throws Exception {
        String url = data.getString("url");
        String param = data.getString("param");
        String postMethod = HttpUtil.post(url, param);
        return Result.success(postMethod);
    }

    private Object executeSql(String sql) throws SQLException {
        Object res = new Object();
        sql = sql.trim();
        Connection conn = null;
        String preOper = "i,I,u,U,d,D";
        try {
            conn = dataSource.getConnection();
            conn.setAutoCommit(false);
            PreparedStatement pstat = conn.prepareStatement(sql);
            String startCh = sql.charAt(0) + "";
            if (!preOper.contains(startCh)) {
                log.info("executeQuery-返回单个 ResultSet 对象_" + startCh);
                ResultSet resultSet = pstat.executeQuery();
                List<Map<String, Object>> convertList = DbUtil.convertToList(resultSet);
                res = convertList;
            } else {
                log.info("executeUpdate->该语句可能为 INSERT、UPDATE 或 DELETE 语句，或者不返回任何内容的 SQL 语句_" + startCh);
                int status = pstat.executeUpdate();
                res = status;
            }
            conn.commit();
        } catch (SQLException e) {
            conn.rollback();
            res = null;
            log.error("执行异常", e);
        } finally {
            conn.close();
        }
        return res;
    }
}
